package com.pubsys.main.server.database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;


/**
 * A database wrapper for the web site selects. This class should not receive any complementing
 * methods
 * @author Martin Reus
 *
 */
public final class Database {	
	public static final String DRIVER = "org.postgresql.Driver";
	public static final String URL = "jdbc:postgresql://ec2-107-20-16-179.compute-1.amazonaws.com:5432/sisweb";
	public static final String USER = "sisweb_user";
	public static final String PASSWORD = "1qazse432";
	
	private static Database databaseInstance = null;
	
	/**
	 * Do not instantiate this class. Instead, use the method {@link getDatabaseInstance()}
	 */
	private Database() {
		
	}
	
	/**
	 * Singleton instance of the Database adaptor.
	 * @see http://pt.wikipedia.org/wiki/Singleton for further reference on the singleton pattern
	 * @return An instance of this class
	 */
	public static Database getDatabaseInstance(){
		if (databaseInstance == null){
			databaseInstance = new Database();
		}
		return databaseInstance;
	}
	
	/**
	 * Returns a connection handler to the database
	 * @return
	 * @throws SQLException
	 */
	private Connection getConnection() throws SQLException{
		Connection con = null;

		try {
			Class.forName(DRIVER).newInstance();
		} catch (Exception e) {
			throw new SQLException("Could not instantiate database class adaptor" );
		}
		
		try {
			con = DriverManager.getConnection(URL, USER, PASSWORD);
		} catch (SQLException e) {
			e.printStackTrace();
			throw new SQLException("Could not connect to the database" );
		}

		return con;

	}
	
	
	/**
	 * To do a select, we provide an instance of ResultSetDataFetcher, which has a method
	 * that should be implemented in order to retrieve the data from the select. It is 
	 * done this way because it encapsulates the database connection and resultSet 
	 * connection, which has to be closed after the data is retrieved.
	 * 
	 * @param dataFetcherInstance an instance of ResultSetDataFetcher<TYPE>
	 * @throws SQLException
	 */
	public void doSelect(AbstractDataFetcher<?> dataFetcherInstance) throws SQLException{
		
		String selectionString = dataFetcherInstance.getSelectionString();
		
		Connection connection = this.getConnection();		
		Statement stmt = connection.createStatement();
		
		try{
			ResultSet resultSet = stmt.executeQuery(selectionString);
			try{
				dataFetcherInstance.fetchDataFromResultSet(resultSet);
			}finally{
				resultSet.close();
			}
		}finally{
			connection.close();
		}		
	}
	
	
	
	/**
	 * faz um select simples
	 * @param selectionString
	 * @return
	 * @throws SQLException
	 */
	public ResultSet doSelect(String selectionString) throws SQLException{
		
		Connection connection = this.getConnection();		
		Statement stmt = connection.createStatement();
		ResultSet resultSet;
		
		resultSet = stmt.executeQuery(selectionString);
		
		
		return resultSet;
		
	}
	
	
	
	
	
	/**
	 * 
	 * @param table
	 * @param fields
	 * @param values
	 * @param returning
	 * @return o ultimo id inserido no banco, se returning != null ou o numero de valores inseridos, se nulo
	 * @throws SQLException
	 */
	public int insert(String table, String fields, String  values, String ret) throws SQLException{
		Connection con = this.getConnection();
		String returning;
		if (ret != null && !ret.equals(""))
			returning = " RETURNING " + "" + ret + "";
		else
			returning = "";
		
		PreparedStatement stmt = con.prepareStatement("insert into " + table + "(" + fields + ")" + " values " + "(" + values + ") " + returning);

		int insertedRows = 0;
		
		if (returning != null && !returning.equals("")){
			ResultSet coisa = stmt.executeQuery();
			if (coisa.next())
				insertedRows = coisa.getInt(ret );
		}else{
			insertedRows = stmt.executeUpdate();
		}
				
		stmt.close();
		con.close();
		return insertedRows;
	}
	
	
}
